create or replace package pkg_batch_mer_main is

  -- Author  : JINJJ
  -- Created : 2017/5/9 13:35:50
  -- Purpose : pkg_batch_mer_main
  --v101 2017.6.6新增注销时删除关联记录 0 法人身份证 1 营业执照  2 结算卡号  3 非法人身份证
  procedure pmerBatchOperation(v_merno_remark in clob, --商户号和备注信息
                              v_ope_type     in varchar2, --操作类型 0 冻结 1解冻 2 注销 3开通
                              v_ope_code     in varchar2, --操作员
                              orr_code       out varchar2, --输出代码
                              orr_desc       out varchar2 --输出内容
                              );
   --占比统计批处理逻辑处理                          
  procedure p_account_statistics_main;  
  --占比统计插入数据
  procedure p_account_statistics_insert(v_execute_date in varchar2);                            


end pkg_batch_mer_main;
/
create or replace package body pkg_batch_mer_main is
  --商户批量操作
  procedure pmerBatchOperation(v_merno_remark in clob, --商户号和备注信息
                              v_ope_type     in varchar2, --操作类型 0 冻结 1解冻 2 注销 3开通
                              v_ope_code     in varchar2, --操作员
                              orr_code       out varchar2, --输出代码
                              orr_desc       out varchar2 --输出内容
                              ) is
    v1              varchar2(10); --分隔符#
    v2              varchar2(10); --分隔符@
    v3              varchar2(100); --一条记录
    v_loc           number(10); --存储字符串位置
    v_count         number(10); --传入的参数长度
    lv_count        number(10); --v_merno_remark是lv_limit的倍数
    i               NUMBER(10); --循环次数
    n               NUMBER(10); --循环位置
    lv_limit        number(5); --每次循环的字段数
    v_merno_remark1 varchar2(4000); --每次循环的字符串
    v_change_flag   varchar2(1000);--change——log表标识 
    v_merinfo_flag  varchar2(1000);--info表标识
    v_change_sql    varchar2(4000); --插入商户变更sql
    v_mcht_inf_sql  varchar2(4000); --更新商户信息sql
    v_syslog_sql    varchar2(4000); --新增日志记录sql
  begin
    orr_code := '000';
    orr_desc := '操作成功';
    v1       := '#';
    v2       := '@';
    i        := 1;
    n        := 1;
    lv_limit := 3000; --起始分割数
    --判断是否为空
    IF v_merno_remark IS NULL THEN
      orr_code := '111';
      orr_desc := '导入数据为空';
      RETURN;
    END IF;
    v_count  := dbms_lob.getlength(v_merno_remark); --传入的参数长度
    lv_count := ceil(v_count / lv_limit) + 1; --v_merno_remark是lv_limit的倍数
    delete from batch_tmp; --删除临时表数据
    --第一层循环（判断分为几个字符串）
    WHILE i < lv_count loop
      IF i = lv_count - 1 THEN
        --最后一次
        lv_limit := MOD(dbms_lob.getlength(v_merno_remark), lv_limit); --余数，最后一次的字符串长度
      END IF;
      --临时字符串
      v_merno_remark1 := substr(v_merno_remark, n, lv_limit);
      --拆分字符串begin
      v_loc := instr(v_merno_remark1, v1); --找到第一次出现的位置
      while v_loc >= 1 loop
        v3 := substr(v_merno_remark1, 1, v_loc - 1); --取出第一条记录
        --插入临时表
        insert into batch_tmp
          (mer_no, remark)
          select substr(v3, 1, instr(v3, v2) - 1),
                 substr(v3, instr(v3, v2) + 1, length(v3))
            from dual;
        v_merno_remark1 := substr(v_merno_remark1,
                                  v_loc + 1,
                                  length(v_merno_remark1) - v_loc); --下一次截取临时字符串
        v_loc           := instr(v_merno_remark1, v1); --下一次起始位置
      end loop;
      ---拆分字符串end
      n := n + lv_limit; --下一次循环的开始位置
      i := i + 1;
    end loop;
    commit;
    --过滤导入商户不符合相应操作的商户
    if v_ope_type='0' then-- 0 冻结
      v_change_flag:='(select 1 from tbl_mcht_inf m where m.mcht_cd = bt.mer_no and m.status in ('||'''02'''||', '||'''03'''||'))'; 
      v_merinfo_flag:='and t.status not in ('||'''02'''||', '||'''03'''||')';   
    elsif v_ope_type='1' then -- 1 解冻
      v_change_flag:='(select 1 from tbl_mcht_inf m where m.mcht_cd = bt.mer_no and m.status in ('||'''02'''||', '||'''01'''||'))';
      v_merinfo_flag:='and t.status not in ('||'''02'''||', '||'''01'''||')';   
    elsif v_ope_type='2' then -- 2 注销
      v_change_flag:='(select 1 from tbl_mcht_inf m where m.mcht_cd = bt.mer_no and m.status in ('||'''02'''||'))';
      v_merinfo_flag:='and t.status <> ('||'''02'''||')';   
    elsif v_ope_type='3' then -- 3 开通
      v_change_flag:='(select 1 from tbl_mcht_inf m where m.mcht_cd = bt.mer_no and m.status in ('||'''01'''||', '||'''03'''||'))';
      v_merinfo_flag:='and t.status not in ('||'''01'''||', '||'''03'''||')';   
    end if;
    --v101begin
    --注销时删除关联记录 0 法人身份证 1 营业执照  2 结算卡号  3 非法人身份证 
    if v_ope_type='2' then
      for msinfo in(select mi.mcht_cd,
                           mi.certif_no,--法人身份证
                           mi.busi_lice_no,--营业执照
                           mb.account,--结算卡号
                           decode(mi.dvp_by, '1', mi.stlm_way_desc, null) stlm_way_desc--非法人身份证
                      from batch_tmp bt, tbl_mcht_inf mi, TBL_MCHT_BANKACCOUNT mb
                      where bt.mer_no = mi.mcht_cd
                       and mi.mcht_cd = mb.owner_cd
                       and mi.status in('01','03'))loop
         --删除0 法人身份证关联记录              
         delete t_merinfo_stop ms where ms.status = '0' and ms.mid=msinfo.certif_no;
         --删除1 营业执照关联记录   
         delete t_merinfo_stop ms where ms.status = '1' and ms.mid=msinfo.busi_lice_no;
         --删除2 结算卡号关联记录   
         delete t_merinfo_stop ms where ms.status = '2' and ms.mid=msinfo.account;
         --删除3 非法人身份证关联记录   
         delete t_merinfo_stop ms where ms.status = '3' and ms.mid=msinfo.stlm_way_desc; 
      end loop;
    end if;
    --v101 end
    --插入商户变更表
    v_change_sql:=' insert into t_merinfo_status_change
                     (id, detail, agent_id, agent_name, createtime, status, mid)
                    select t_merinfo_status_change_seq.nextval id,
                           bt.remark detail,
                           (select t.id from t_operators t where t.account_no='''||v_ope_code||''') agent_id,
                           (select t.real_name from t_operators t where t.account_no='''||v_ope_code||''') agent_name,
                           to_char(sysdate, '||'''yyyy-mm-dd hh24:mi:ss'''||') createtime,
                           decode('''||v_ope_type||''', '||'''0'''||', '||'''0'''||', '||'''2'''||','||'''9'''||','||'''1'||''') status, 
                           bt.mer_no
                      from batch_tmp bt 
                      where not exists'||v_change_flag;
     DBMS_OUTPUT.PUT_LINE('v_change_sql的值是'||v_change_sql); 
     execute immediate v_change_sql;              
    --新增日志记录--状态 052冻结 051解冻 081注销 093开通
     v_syslog_sql:='insert into t_syslog
                  (id,
                   operators,
                   oper_code,
                   oper_name,
                   log_context,
                   dt_create,
                   dt_lastmod)
                  select log_seq.nextval id,
                         '''||v_ope_code||''' operators,
                         decode('''||v_ope_type||''', '||'''0'''||','||'''052'''||','||'''1'''||', '||'''051'''||','||'''2'''||', '||'''081'''||','||'''093'''||') oper_code,
                         decode('''||v_ope_type||''',
                                '||'''0'''||', 
                                '||'''特约商户冻结'''||',
                                '||'''1'''||',
                                '||'''特约商户解冻'''||',
                                '||'''2'''||',
                                '||'''特约商户注销'''||',
                                '||'''特约商户开通'''||') oper_name,  
                         '||'''操作员【' || v_ope_code || '】,已''||'||
                         'decode('''||v_ope_type||''', '||'''0'''||', '||'''冻结'''||', '||'''1'''||', '||'''解冻'''||','||'''2'''||','||'''注销'''||','||'''开通'''||')||'||
                         '''【 '''||'||bt.mer_no||'||''' 】成功''  log_context,
                         cast(sysdate as timestamp),
                         cast(sysdate as timestamp) 
                    from batch_tmp bt
                    where  not exists'||v_change_flag;
        DBMS_OUTPUT.PUT_LINE('v_syslog_sql的值是'||v_syslog_sql); 
        execute immediate v_syslog_sql;  
        --更新商户信息表 --商户表中  status  03冻结 02 注销   01 正常（解冻）
        v_mcht_inf_sql:='update tbl_mcht_inf t
                         set t.status     = decode('''||v_ope_type||''','||'''0'''||', '||'''03'''||','||'''2'''||','||'''02'''||','||'''01'''||'),
                             t.rec_upd_ts = cast(sysdate as timestamp),
                             t.rec_opr_id = '||'''U'''||'
                       where exists (select 1 from batch_tmp b where b.mer_no=t.mcht_cd)
                       '||v_merinfo_flag;
         DBMS_OUTPUT.PUT_LINE('v_mcht_inf_sql的值是'||v_mcht_inf_sql); 
         execute immediate v_mcht_inf_sql;            
    --异常处理
  Exception
    When others then
      orr_code := '999';
      orr_desc := '操作失败,' || SQLERRM;
      ROLLBACK;
  End;

  --占比统计批处理逻辑处理
  procedure p_account_statistics_main as
   v_cnt_log  number(8);
   v_finish_date varchar2(10);
   v_state_date  varchar2(10);
   v_fail_log    varchar2(1000);
   begin
      select to_char(sysdate - 1, 'yyyymmdd') into v_finish_date from dual;
      select count(1) into v_cnt_log from TBL_ACCOUNT_STATISTICS_LOG;
      if v_cnt_log = 0 then
        v_state_date := '20170701';--从7月1日开始统计
      else
        select to_char(to_date(max(l.sub_time), 'yyyymmdd') + 1, 'yyyymmdd')
          into v_state_date
          from TBL_ACCOUNT_STATISTICS_LOG l;
      end if;
      while v_state_date<=v_finish_date loop
        --插入占比统计表
        p_account_statistics_insert(v_state_date);
        v_state_date:=to_char(to_date(v_state_date, 'yyyymmdd') + 1, 'yyyymmdd');
      end loop;
    --异常处理
   Exception
    When others then
      v_fail_log := '操作失败,' || SQLERRM; 
      ROLLBACK;
      insert into TBL_ACCOUNT_STATISTICS_LOG
        (as_log_id,
        recode_time,
        sub_time,
        as_log_desc 
        )values(
        SEQ_ACCOUNT_STATISTICS_LOG.NEXTVAL,
        to_char(sysdate,'yyyymmdd'),
        '',
        v_fail_log
        );
   End;
   --占比统计插入
   procedure p_account_statistics_insert(v_execute_date in varchar2) as
    v_log_id number(8);
   begin
     ----插入占比统计表
     insert into tbl_accounting_statistics_info
     (as_id,                    
      trans_dt,                 
      rout_industry_insid_cd,   
      ext1_name,                
      prov_cd,                  
      city_cd,                  
      mcht_cd,                  
      appl_date,                
      status,                   
      credit_rate,              
      brush_rate,               
      business_rate,            
      high_return_cnt,          
      high_risk_return_rate,    
      cnt_success,              
      cnt_success_amt,          
      cnt_success_rate,         
      cnt_fail,                 
      cnt_failed_rate,          
      night_success_cnt,        
      night_success_amt,        
      night_failed_cnt,         
      night_cnt,                
      night_failed_rate,        
      balance_query_failed_cnt, 
      balance_query_cnt,        
      balance_query_failed_rate)
      select seq_accounting_statistics_info.nextval id,temp.* 
      from ( 
      SELECT
      CASE
         WHEN T.T1_TRANS_DT IS NOT NULL THEN
          T.T1_TRANS_DT
         ELSE
          T.T2_TRANS_DT
       END TRANS_DT,
       CASE
         WHEN T.T1_ROUT_INDUSTRY_INS_ID_CD IS NOT NULL THEN
          T.T1_ROUT_INDUSTRY_INS_ID_CD
         ELSE
          T.T2_ROUT_INDUSTRY_INS_ID_CD
       END ROUT_INDUSTRY_INS_ID_CD,
       T5.REAL_NAME,
       T.PROV_CD,
       T.CITY_CD,
       T.MCHT_CD,
       T.APPL_DATE,
       T.STATUS,
       CASE
         WHEN T.M > 0 THEN
          ROUND((T.M / T.O) * 100, 2) || '%'
         ELSE
          '0.00%'
       END credit_rate,
       CASE
         WHEN T.N > 0 THEN
          ROUND((T.N / T.O) * 100, 2) || '%'
         ELSE
          '0.00%'
       END brush_rate,
       CASE
         WHEN T.O > 0 THEN
          T.O
         ELSE
          0
       END business_rate,
       CASE
         WHEN T.P > 0 THEN
          T.P
         ELSE
          0
       END high_return_cnt ,
       CASE
         WHEN T.P > 0 THEN
          ROUND((T.P / T.O) * 100, 2) || '%'
         ELSE
          '0.00%'
       END high_risk_return_rate ,
       CASE
         WHEN T.R > 0 THEN
          T.R
         ELSE
          0
       END cnt_success ,
       CASE
         WHEN T.S > 0 THEN
          ROUND(T.S / 100, 2)
         ELSE
          0.00
       END cnt_success_amt ,
       CASE
         WHEN T.R > 0 THEN
          ROUND((T.R / T.O) * 100, 2) || '%'
         ELSE
          '0.00%'
       END cnt_success_rate ,
       CASE
         WHEN T.U > 0 THEN
          T.U
         ELSE
          0
       END cnt_fail ,
       CASE
         WHEN T.U > 0 THEN
          ROUND((T.U / T.O) * 100, 2) || '%'
         ELSE
          '0.00%'
       END cnt_failed_rate ,
       CASE
         WHEN T.AE > 0 THEN
          T.AE
         ELSE
          0
       END night_success_cnt ,
       CASE
         WHEN T.AF > 0 THEN
          T.AF
         ELSE
          0
       END night_success_amt ,
       CASE
         WHEN T.AG > 0 THEN
          T.AG
         ELSE
          0
       END night_failed_cnt ,
       CASE
         WHEN T.AH > 0 THEN
          T.AH
         ELSE
          0
       END night_cnt ,
       CASE
         WHEN T.AH > 0 THEN
          ROUND((T.AG / T.AH) * 100, 2) || '%'
         ELSE
          '0%'
       END night_failed_rate ,
       T.AJ balance_query_failed_cnt ,
       T.AI balance_query_cnt ,
       CASE
         WHEN T.AI > 0 THEN
          ROUND((T.AJ / T.AI) * 100, 2) || '%'
         ELSE
          '0%'
       END balance_query_failed_rate 
  FROM (SELECT *
          FROM (SELECT *
                  FROM (SELECT A.MCHNT_CD AS T1_MCHT_CD,
                               A.TRANS_DT AS T1_TRANS_DT,
                               A.ROUT_INDUSTRY_INS_ID_CD AS T1_ROUT_INDUSTRY_INS_ID_CD,
                               SUM(CASE
                                     WHEN A.CARD_CLASS <> '01' THEN
                                      1
                                     ELSE
                                      0
                                   END) M,
                               SUM(CASE
                                     WHEN A.POS_ENTRY_MD_CD LIKE '02%' THEN
                                      1
                                     ELSE
                                      0
                                   END) N,
                               SUM(CASE
                                     WHEN A.RESP_CD IN ('33',
                                                        '34',
                                                        '35',
                                                        '36',
                                                        '41',
                                                        '43',
                                                        '51',
                                                        '54',
                                                        '55',
                                                        '57',
                                                        '59',
                                                        '60',
                                                        '61',
                                                        '62',
                                                        '67',
                                                        '75') THEN
                                      1
                                     ELSE
                                      0
                                   END) P,
                               SUM(CASE
                                     WHEN '1' = '1' THEN
                                      1
                                     ELSE
                                      0
                                   END) O,
                               SUM(CASE
                                     WHEN (A.RESP_CD = '00' AND
                                          A.TRANS_ST = '1000000000') THEN
                                      1
                                     ELSE
                                      0
                                   END) R,
                               SUM(CASE
                                     WHEN (A.RESP_CD <> '00') THEN
                                      1
                                     ELSE
                                      0
                                   END) U,
                               SUM(CASE
                                     WHEN (A.RESP_CD = '00' AND
                                          A.TRANS_ST = '1000000000') THEN
                                      to_char(A.TRANS_AT)
                                     ELSE
                                      '0'
                                   END) S,
                               SUM(CASE
                                     WHEN ((A.TRANS_MT BETWEEN '000000' AND
                                          '060000') AND A.TRANS_ST = '1000000000') THEN
                                      1
                                     ELSE
                                      0
                                   END) AE,
                               SUM(CASE
                                     WHEN ((A.TRANS_MT BETWEEN '000000' AND
                                          '060000') AND A.TRANS_ST = '1000000000') THEN
                                      to_char(A.TRANS_AT)
                                     ELSE
                                      '0'
                                   END) AF,
                               SUM(CASE
                                     WHEN ((A.TRANS_MT BETWEEN '000000' AND
                                          '060000') AND A.RESP_CD <> '00') THEN
                                      1
                                     ELSE
                                      0
                                   END) AG,
                               SUM(CASE
                                     WHEN (A.TRANS_MT BETWEEN '000000' AND '060000') THEN
                                      1
                                     ELSE
                                      0
                                   END) AH
                          FROM hbat.tbl_tfr_trn_log A
                         WHERE A.TRANS_DT =v_execute_date
                           AND A.INDUSTRY_INS_ID_CD = '48502000'
                           AND A.MA_TRANS_CD IN ('1011', '1171')
                         GROUP BY A.MCHNT_CD,
                                  A.TRANS_DT,
                                  A.ROUT_INDUSTRY_INS_ID_CD) T1
                  FULL OUTER JOIN (SELECT A.MCHNT_CD AS T2_MCHT_CD,
                                         A.TRANS_DT AS T2_TRANS_DT,
                                         A.ROUT_INDUSTRY_INS_ID_CD AS T2_ROUT_INDUSTRY_INS_ID_CD,
                                         SUM(CASE
                                               WHEN (A.RESP_CD <> '00') THEN
                                                1
                                               ELSE
                                                0
                                             END) AJ,
                                         SUM(CASE
                                               WHEN '1' = '1' THEN
                                                1
                                               ELSE
                                                0
                                             END) AI
                                    FROM hbat.tbl_tfr_trn_log A
                                   WHERE A.TRANS_DT = v_execute_date
                                     AND A.INDUSTRY_INS_ID_CD = '48502000'
                                     AND A.MA_TRANS_CD IN ('1001')
                                   GROUP BY A.MCHNT_CD,
                                            A.TRANS_DT,
                                            A.ROUT_INDUSTRY_INS_ID_CD) T2
                    ON T1.T1_MCHT_CD = T2.T2_MCHT_CD) T3
          LEFT JOIN APSTFR.TBL_MCHT_INF B
            ON (T3.T1_MCHT_CD = B.MCHT_CD OR T3.T2_MCHT_CD = B.MCHT_CD)) T,
       APSMGM.T_OPERATORS T5
 WHERE T.EXT1 = T5.ID
 order by t.mcht_cd )temp;
--查询记录表序列
 select SEQ_ACCOUNT_STATISTICS_LOG.NEXTVAL into v_log_id from dual;
 --插入占比统计记录表
 insert into TBL_ACCOUNT_STATISTICS_LOG
 (as_log_id,
  recode_time,
  sub_time,
  as_log_desc 
  )values(
  v_log_id,
  to_char(sysdate,'yyyymmdd'),
  v_execute_date,
  v_execute_date||'跑批成功'
  );
  commit;
   End;
end pkg_batch_mer_main;
/
